from django.shortcuts import render, HttpResponse
from django.db.models import Avg, Sum, Count, Q, Max, Min, F
from .models import Student, Course, Score, Teacher


def index(request):
    # 1.查询平均成绩大于60分的同学的id和平均成绩；
    students = Student.objects.annotate(avg_score=Avg('score__number')).filter(avg_score__gt=60).values('id',
                                                                                                        'avg_score')
    for student in students:
        print(student.get('id'), student.get('avg_score'))
    print(students.query)
    return HttpResponse('index')


def index2(request):
    # 2.查询所有同学的id、姓名、选课的数量、总成绩
    students = Student.objects.annotate(total_course=Count('score__id'), total_score=Sum('score__number')) \
        .values('id', 'name', 'total_course', 'total_score')
    for student in students:
        print('id={id}, name={name}, course_total={total_course}, score_total={total_score}'.
              format(id=student.get('id'), name=student.get('name'), total_course=student.get('total_course'),
                     total_score=student.get('total_score')))
    print(students.query)
    return HttpResponse('index2')


def index3(request):
    # 3.查询姓“李”的老师的个数
    result = Teacher.objects.filter(name__startswith='李').count()
    print('查询姓“李”的老师的个数：%s' % result)
    return HttpResponse('index3')


def index4(request):
    # 4.查询没学过“李老师”课的同学的id、姓名
    students = Student.objects.exclude(score__course__teacher__name='李老师').values('id', 'name')
    for student in students:
        print('%s/%s' % (student.get('id'), student.get('name')))
    print(students)
    print(students.query)
    return HttpResponse('index4')


def index5(request):
    # 5.查询学过课程id为1和2的所有同学的id、姓名
    students = Student.objects.filter(score__course__id__in=[1, 2]).distinct().values('id', 'name')
    for student in students:
        print(student)
    print(students)
    print(students.query)
    return HttpResponse('index5')


def index6(request):
    # 6.查询学过“黄老师”所教的“所有课”的同学的id、姓名；
    # 1. 首先先找到每一位学生学习的黄老师课程的数量；A
    # Student.objects.annotate(num=Count('score__course__teacher',filter=Q(score__course__teacher__name='黄老师')))
    # 2. 在课程的表中找到黄老师教的课程的数量；B
    # Course.objects.filter(teacher__name='黄老师').count()
    # 3. 判断A是否等于B，如果相等，那么意味着这位学生学习了黄老师教的
    # Student.objects.annotate(num=Count('score__course__teacher',filter=Q(score__course__teacher__name='黄老师'))).
    # filter(num=Course.objects.filter(teacher__name='黄老师').count())
    # 所有课程，如果不想等，那么意味着这位学生没有学完黄老师教的所有课程
    students = Student.objects.annotate(num=Count('score__course__teacher',
                                                  filter=Q(score__course__teacher__name='黄老师'))). \
        filter(num=Course.objects.filter(teacher__name='黄老师').count()).values('id', 'name', 'num')
    for student in students:
        print(student)
    print(students.query)
    return HttpResponse('index6')


def index7(request):
    # 7.查询所有课程成绩小于60分的同学的id和姓名；
    students = Student.objects.exclude(score__number__gt=60).values('id', 'name')
    for student in students:
        print(student)
    print(students.query)
    return HttpResponse('index7')


def index8(request):
    # 8.查询没有学全所有课的同学的id、姓名

    # 8.1 先统计每个学生学习课程的数量：A
    # 8.2 先统计所有课程数量：B
    # 如果 A=B，代表学完了所有课程。反之，没学完
    # students = Student.objects.annotate(num=Count('score__course')).exclude(num=Course.objects.all().count()) \
    #     .values('id', 'name', 'num')

    students = Student.objects.annotate(num=Count('score__course')).filter(num__lt=Course.objects.all().count())\
        .values('id', 'name', 'num')
    for student in students:
        print(student)
    print(students.query)
    return HttpResponse('index8')


def index9(request):
    # 9.查询所有学生的姓名、平均分，并且按照平均分从高到低排序
    students = Student.objects.annotate(avg_score=Avg('score__number')).order_by('-avg_score') \
        .values('id', 'name', 'avg_score')
    for student in students:
        print(student)
    print(students.query)
    return HttpResponse('index9')


def index10(request):
    # 10.查询各科成绩的最高和最低分，以如下形式显示：课程ID，课程名称，最高分，最低分
    courses = Course.objects.annotate(max_score=Max('score__number'), min_score=Min('score__number')) \
        .values('id', 'name', 'max_score', 'min_score')
    for course in courses:
        print(course)
    print(courses.query)
    return HttpResponse('index10')


def index11(request):
    # 11.查询没门课程的平均成绩，按照平均成绩进行排序
    courses = Course.objects.annotate(avg_score=Avg('score__number')).order_by('-avg_score') \
        .values('id', 'name', 'avg_score')
    for course in courses:
        print(course)
    print(courses.query)
    return HttpResponse('index11')


def index12(request):
    # 12.统计总共有多少女生，多少男生
    # boys = Student.objects.filter(gender=1).count()
    # girls = Student.objects.filter(gender=2).count()
    students = Student.objects.aggregate(boys=Count('gender', filter=Q(gender=1)),
                                         girls=Count('gender', filter=Q(gender=2)))
    print('boys: %s, girls: %s' % (students.get('boys'), students.get('girls')))
    return HttpResponse('index12')


def index13(request):
    # 13.将“黄老师”的每一门课程都在原来的基础之上加5分
    # 13.1 先统计黄老师课程的所有成绩记录
        # Score.objects.filter(course__teacher__name='黄老师')
    # 13.2 再将每条成绩记录 分数 + 5
    scores = Score.objects.filter(course__teacher__name='黄老师').update(number=F('number') + 5)
    print(scores)
    return HttpResponse('index13')


def index14(request):
    # 14.查询两门以上不及格的同学的id、姓名、以及不及格课程数；
    # 14.1 先统计每个同学不及格科目数
        # Student.objects.annotate(low_score_nums=Count('score', filter=Q(score__number__lt=60)))
    # 14.2 在过滤出不及格课程数两门以上的
    students = Student.objects.annotate(low_score_nums=Count('score', filter=Q(score__number__lt=60))).\
        filter(low_score_nums__gte=2).values('id', 'name', 'low_score_nums')
    for student in students:
        print(student)
    print(students.query)
    return HttpResponse('index14')


def index15(request):
    # 15.查询每门课的选课人数
    courses = Course.objects.annotate(nums=Count('score')).values('id', 'name', 'nums')
    for course in courses:
        print(course)
    print(courses.query)
    return HttpResponse('index15')
